package util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel导出工具
 * 
 * @author Administrator
 *
 */
public class ExcelUtil {
	/**
	 * 私有方法，判断excel文件格式初始化工作簿
	 * 
	 * @param filepath
	 *            文件路径
	 * @return 返回工作簿
	 * @throws IOException
	 */
	private static Workbook initType(String filepath) throws IOException {
		InputStream is = new FileInputStream(filepath);
		Workbook wb = null;
		if (filepath != null) {
			if (filepath.endsWith(".xls")) {
				wb = new HSSFWorkbook(is);
			} else if (filepath.endsWith(".xlsx")) {
				wb = new XSSFWorkbook(is);
			} else {

			}
		}
		if (null != is) {
			is.close();
		}
		return wb;
	}

	/**
	 * 
	 * @param filepath
	 * @param startRow
	 * @param endRow
	 * @return
	 */
	public static List<String[]> excelToList(String filepath, int startRow, int endRow) {
		return excelToList(filepath, startRow, endRow, 0);
	}

	/**
	 * excel空白单元格内容为null值设"",默认读取表中第一个sheet。
	 * 
	 * @param filepath
	 *            文件路径
	 * @param startRow
	 *            起始行
	 * @param endRow
	 *            结束行
	 * @param sheetNo
	 *            工作簿编号 起始值为0
	 * @return
	 */
	public static List<String[]> excelToList(String filepath, int startRow, int endRow, int sheetNo) {
		List<String[]> list = new ArrayList<String[]>();
		Workbook wb = null;
		try {
			wb = initType(filepath);
		} catch (IOException e1) {
			return null;
		}
		if (null != wb) {
			try {
				// 获取表中第一个sheet
				Sheet sh = wb.getSheetAt(sheetNo);
				int rows = sh.getLastRowNum() + 1 - sh.getFirstRowNum(); // 总行数
				endRow = rows > endRow ? endRow : rows; // 保证endRow不大于总行数
				for (int i = startRow; i < endRow; i++) {
					Row row = sh.getRow(i);
					int cols = row.getLastCellNum(); // 该行的总列数
					String[] str = new String[cols]; // 用来存放该行每一列的值
					for (int j = 0; j < cols; j++) {
						if (isMergedRegion(sh, i, j)) {
							str[j] = getMergedRegionValue(sh, i, j);
						} else {
							Cell cell = row.getCell(j);
							str[j] = getCellValue(cell);
						}
					}
					list.add(str);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 判断指定的单元格是否是合并单元格
	 * 
	 * @param sheet
	 * @param row
	 *            行下标
	 * @param column
	 *            列下标
	 * @return
	 */
	private static boolean isMergedRegion(Sheet sheet, int row, int column) {
		sheet.getNumMergedRegions();
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return true;
				}
			}
		}
		return false;
	}

	/**
	 * 获取合并单元格的值
	 * 
	 * @param sheet
	 * @param row
	 * @param column
	 * @return
	 */
	private static String getMergedRegionValue(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					Row fRow = sheet.getRow(firstRow);
					Cell fCell = fRow.getCell(firstColumn);
					return getCellValue(fCell);
				}
			}
		}
		return null;
	}
	/**
	 * 获取单元格数据
	 * @param cell
	 * @return
	 */
	private static String getCellValue(Cell cell) {
		if (null != cell) {
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) { // 日期处理
					double d = cell.getNumericCellValue();
					Date date = HSSFDateUtil.getJavaDate(d);
					SimpleDateFormat dformat = new SimpleDateFormat("yyyy/MM/dd");
					return dformat.format(date).trim();
				} else {
					DecimalFormat df = new DecimalFormat("0"); // 避免科学计数法
					return df.format(cell.getNumericCellValue()).trim();
				}
			case HSSFCell.CELL_TYPE_STRING:
				return cell.getStringCellValue().trim();
			default:
				return "";
			}
		}
		return "";
	}

}
